package com.threeti.mecool.core.application.project;

import com.threeti.mecool.core.application.Cache;
import com.threeti.mecool.core.application.SearchService;
import com.threeti.mecool.core.domain.model.acl.OrgnizationEmployee;
import com.threeti.mecool.core.domain.model.acl.User;
import com.threeti.mecool.core.domain.model.basicdata.Brand;
import com.threeti.mecool.core.domain.model.dto.AssignableProjectStoreDto;
import com.threeti.mecool.core.domain.model.project.*;
import com.threeti.mecool.core.infrastructure.persistence.CommunicationRepository;
import com.threeti.mecool.core.infrastructure.persistence.project.*;
import com.threeti.mecool.framework.util.JpaUtil;
import com.threeti.mecool.framework.util.Lists;
import com.threeti.mecool.framework.util.Maps;
import com.threeti.mecool.framework.util.Sets;
import com.threeti.mecool.legacy.domain.model.basicdata.AdminDivisionLegacy;
import com.threeti.mecool.legacy.domain.model.basicdata.BrandLegacy;
import com.threeti.mecool.legacy.domain.model.basicdata.ProductLegacy;
import com.threeti.mecool.legacy.domain.model.basicdata.biz.SalesLegacy;
import com.threeti.mecool.legacy.infrastructure.persistence.AdminDivisionLegacyRepository;
import com.threeti.mecool.legacy.infrastructure.persistence.BrandLegacyRepository;
import com.threeti.mecool.legacy.infrastructure.persistence.ProductLegacyRepository;
import com.threeti.mecool.legacy.infrastructure.persistence.SalesLegacyRepository;
import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.domain.Specifications;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.CollectionUtils;

import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import javax.persistence.criteria.*;
import javax.persistence.criteria.CriteriaBuilder.In;
import java.util.*;

@Service
public class ProjectServiceImpl implements ProjectService {

    public static final Logger logger = LoggerFactory.getLogger(ProjectServiceImpl.class);

    @Autowired
    Cache cache;

    @Autowired
    private ProjectRepository projRepo;

    @Autowired
    private ProjectEmployeeRepository projEmpRepo;

    @Autowired
    private ProjectSalesPromoterRepository projSalesRepo;

    @Autowired
    private ProjectStoreRepository projStoreRepo;

    @Autowired
    private EntityManagerFactory emFactory;

    @Autowired
    private CommunicationRepository commRepo;

    @Autowired
    private ProductLegacyRepository productLegacyRepo;

    @Autowired
    private BrandLegacyRepository brandLegacyRepo;

    @Autowired
    private ProjectProductRepository projProdRepo;

    @PersistenceContext
    private EntityManager entityManager;

    @Autowired
    private SearchService searchService;

    @Autowired
    private SalesLegacyRepository salesLegacyRepository;

    @Autowired
    private AdminDivisionLegacyRepository adminDivLegacyRepo;
    
    @Autowired
    private JdbcTemplate jdbcTemplate;

    /**
     * SLOOOW
     */
    @Deprecated
    @Override
    public List<Project> findByUserOwn(User user) {
        if (user == null) return Collections.emptyList();

        OrgnizationEmployee orgEmp = user.getEmployee();
        return findByEmployeeOwn(orgEmp);
    }

    @Deprecated
    @Override
    public List<Project> findByEmployeeOwn(OrgnizationEmployee orgEmp) {
        if (orgEmp == null) return Collections.emptyList();
        if (orgEmp.getEmpId() == null) return Collections.emptyList();
        return projRepo.findByParticipatedEmployeeId(orgEmp.getEmpId());
    }

    @SuppressWarnings("unchecked")
    @Override
    public String getUserInvolvedProjectCodeString(String empId) {
        String sql = "select code from dm_project where id in (" + getUserInvolvedProjectIdString(empId) + ")";
        List<String> list = entityManager.createNativeQuery(sql).getResultList();
        StringBuilder s = new StringBuilder(500);
        s.append("'__NONE_EXISTS__'");
        for (String code : list) {
            s.append(",'").append(code).append("'");
        }
        return s.toString();
    }

    @SuppressWarnings("unchecked")
    @Override
    public String getUserInvolvedProjectIdString(String empId) {
        String sql = "select project from dm_project_employee where emp_id=" + empId;
        List<Object> list = entityManager.createNativeQuery(sql).getResultList();
        StringBuilder s = new StringBuilder(list.size() * 11 + 10);
        s.append("-9999"); // none exist
        for (Object projectId : list) {
            s.append(",").append(projectId.toString());
        }
        return s.toString();
    }

    @Override
    @Transactional
    public List<ProjectStore> findStoresPreAssignedToUserWithinTheProject(User user, Long projectId) {
        if (user == null) return Collections.emptyList();

        OrgnizationEmployee orgEmp = user.getEmployee();

        if (orgEmp != null && orgEmp.getEmpId() != null) {
            Project proj = projRepo.findOne(projectId);
            if (proj != null) {
                ProjectEmployee projEmp;

                if ((projEmp = proj.getParticipatedEmployeeBy(orgEmp.getEmpId())) != null) {
                    JpaUtil.forceInitializeCollection(projEmp.getAssignedStores(), emFactory);
                    return projEmp.getAssignedStores();
                }
            }
        }
        return Collections.emptyList();
    }

    @Override
    @Transactional
    public List<ProjectStore> findStoresPreAssignedToSalesWithinTheProject(String salesCode, Long projectId) {
        ProjectSalesPromoter projSales = projSalesRepo.findBySalesPromoterCodeAndProjectId(salesCode, projectId);

        if (projSales == null) return Collections.emptyList();

        JpaUtil.forceInitializeCollection(projSales.getAssignedStores(), emFactory);

        return projSales.getAssignedStores();
    }

    @Override
    @Transactional
    public Project storeProjectEmloyeesHierarchy(Long projectId, List<ProjectEmployeeRawTreeNode> projEmployeesHierarchy) {
        Object[] objs = removeProjectEmployeesAndAllRelatedWithSavedStoresAssignedReturn(projRepo.findOne(projectId));
        Project projWithEmployeesAndRelatedRemoved = (Project)objs[0];
        Map<Long, List<ProjectStore>> savedEmpsWithStoresAssigned = (Map)objs[1];

        List<ProjectEmployee> projEmpsTreeTotallyReBuild = Lists.newArrayList();
        Map<Long, ProjectEmployee> treeRegistry = new HashMap<Long, ProjectEmployee>();
        for (ProjectEmployeeRawTreeNode rawNode : projEmployeesHierarchy) {
            ProjectEmployee emp = new ProjectEmployee(projWithEmployeesAndRelatedRemoved, rawNode.getEmpId(), rawNode.getEmpName(), rawNode.getTitleWithinTheProject(), rawNode.getRankType());
            emp.setId(rawNode.getId());//jay:this is important when doing the temporary memory maintain
            treeRegistry.put(rawNode.getId(), emp);
            projEmpsTreeTotallyReBuild.add(emp);
        }

        for (ProjectEmployeeRawTreeNode rawNode : projEmployeesHierarchy) {
            if (rawNode.getParentId() != null) {
                treeRegistry.get(rawNode.getParentId()).addChild(treeRegistry.get(rawNode.getId()));
            }
        }

        projWithEmployeesAndRelatedRemoved.setParticipatedEmployees(projEmpsTreeTotallyReBuild);//do refresh
        //do stores re-assign to the emps still existed after trees totally refreshed
        for (ProjectEmployee newProjEmp : projEmpsTreeTotallyReBuild) {
            if (newProjEmp.getEmpId() != null) {
                List<ProjectStore> storesAssigedBefore;
                if (!CollectionUtils.isEmpty(storesAssigedBefore = savedEmpsWithStoresAssigned.get(newProjEmp.getEmpId()))) {
                    newProjEmp.assignStores(storesAssigedBefore);
                }
            }
        }

        projWithEmployeesAndRelatedRemoved = projRepo.saveAndFlush(projWithEmployeesAndRelatedRemoved);
        savedEmpsWithStoresAssigned = null;


        return projWithEmployeesAndRelatedRemoved;
    }

    @Override
    @Transactional(readOnly=true)
    public ProjectEmployee findProjectManager(Long projectId) {
        Project project = projRepo.findOne(projectId);
        List<ProjectEmployee> empsTree = project.getParticipatedEmployees();
        for (ProjectEmployee projEmp : empsTree) {
            if (projEmp.getParent() == null) {
                //JpaUtil.forceInitializeCollection(projEmp.getChilds(), emFactory);//jay:useless fetching only one level child,still leads lazy initialization ex
                return projEmp;
            }
        }
        return null;
    }

    @Override
    @Transactional
    public void removeProject(Long projectId) {
        Project targetProject = projRepo.findOne(projectId);
        targetProject = removeProjectEmployeesAndAllRelated(targetProject);

        //删除项目采集项
        projProdRepo.deleteByProjectId(targetProject.getId());

        //删除项目相关的沟通记录
        commRepo.deleteByProjectId(projectId);

        //删除项目员工
        projEmpRepo.deleteByProjectId(projectId);

        //删除项目促销员
        projSalesRepo.deleteByProjectId(projectId);

        //删除项目门店
        projStoreRepo.deleteByProjectId(projectId);

        //删除项目
        projRepo.delete(targetProject);
    }

    @Override
    @Transactional
    public ProjectEmployee assignStoresToProjectEmployee(Long projEmpId, List<Long> targetStoreIds) {
        ProjectEmployee projEmp = projEmpRepo.findOne(projEmpId);
        List<ProjectStore> projectStoresNeedToBeAssigned = (List<ProjectStore>) projStoreRepo.findAll(targetStoreIds);
        return projEmp.assignStores(projectStoresNeedToBeAssigned);
    }

    @Override
    @Transactional
    public ProjectSalesPromoter assignStoresToProjectSalesPromoter(Long projSalesId, List<Long> targetStoreIds) {
        ProjectSalesPromoter projSales = projSalesRepo.findOne(projSalesId);
        List<ProjectStore> projectStoresNeedToBeAssigned = (List<ProjectStore>) projStoreRepo.findAll(targetStoreIds);
        return projSales.assignStores(projectStoresNeedToBeAssigned);
    }

    @Override
    @Transactional
    public ProjectEmployee unbindWithSpecifiedAssignedStore(Long projEmpId, List<Long> projectStoreIdsNeedToBeUnbinded) {
        ProjectEmployee projEmp = projEmpRepo.findOne(projEmpId);
        List<ProjectStore> projectStoresNeedToBeUnbinded = (List<ProjectStore>) projStoreRepo.findAll(projectStoreIdsNeedToBeUnbinded);

        return projEmp.unbindWithSpecifiedAssignedStore(projectStoresNeedToBeUnbinded);
    }

    @Override
    @Transactional
    public ProjectSalesPromoter unbindProjectSalesWithSpecifiedAssignedStore(Long projSalesId, List<Long> projectStoreIdsNeedToBeUnbinded) {
        ProjectSalesPromoter projSales = projSalesRepo.findOne(projSalesId);
        List<ProjectStore> projectStoresNeedToBeUnbinded = (List<ProjectStore>) projStoreRepo.findAll(projectStoreIdsNeedToBeUnbinded);

        return projSales.unbindWithSpecifiedAssignedStore(projectStoresNeedToBeUnbinded);
    }

    @Override
    @Transactional(readOnly=true)
    public Page<ProjectStore> findCandidateProjectStoresWhichCanBeAssigned(Long projId, Pageable pageable) {
        Project project = projRepo.findOne(projId);
        List<ProjectEmployee> projEmps = project.getParticipatedEmployees();
        Set<Long> storeIds = Sets.newHashSet();
        for (ProjectEmployee projEmp : projEmps) {
            List<ProjectStore> projStores = projEmp.getAssignedStores();
            for (ProjectStore projStore : projStores) {
                storeIds.add(projStore.getId());
            }
        }
        if (CollectionUtils.isEmpty(storeIds)) {
            return projStoreRepo.findByProjectId(project.getId(), pageable);
        }

        return projStoreRepo.findByProjectIdAndIdNotIn(project.getId(), storeIds, pageable);
    }

    @Transactional(readOnly=true)
    public Page<ProjectStore> findCandidateProjectStoresWhichCanBeAssignedToSalesPromoter(Long projId, Pageable pageable) {
        Project project = projRepo.findOne(projId);
        List<ProjectSalesPromoter> projSales = project.getParticipatedSalesPromoters();
        Set<Long> storeIds = Sets.newHashSet();
        for (ProjectSalesPromoter projEmp : projSales) {
            List<ProjectStore> projStores = projEmp.getAssignedStores();
            for (ProjectStore projStore : projStores) {
                storeIds.add(projStore.getId());
            }
        }
        if (CollectionUtils.isEmpty(storeIds)) {
            return projStoreRepo.findByProjectId(project.getId(), pageable);
        }

        return projStoreRepo.findByProjectIdAndIdNotIn(project.getId(), storeIds, pageable);
    }



    @Transactional(readOnly=true)
    public Page<ProjectStore> findCandidateProjectStoresWhichCanBeAssignedToSalesPromoter(Long projId,String saleCode, Pageable pageable) {
        Project project = projRepo.findOne(projId);
//    List<ProjectSalesPromoter> projSales = project.getParticipatedSalesPromoters();
//    Set<Long> storeIds = Sets.newHashSet();
//    for (ProjectSalesPromoter projEmp : projSales) {
//      List<ProjectStore> projStores = projEmp.getAssignedStores();
//      for (ProjectStore projStore : projStores) {
//        storeIds.add(projStore.getId());
//      }
//    }

        List<ProjectStore> hasAssignedStores = findStoresPreAssignedToSalesWithinTheProject(saleCode,projId);
        Set<Long> storeIds = Sets.newHashSet();
        for (ProjectStore store : hasAssignedStores) {
            storeIds.add(store.getId());
        }

        if (CollectionUtils.isEmpty(storeIds)) {
            return projStoreRepo.findByProjectId(project.getId(), pageable);
        }

        return projStoreRepo.findByProjectIdAndIdNotIn(project.getId(), storeIds, pageable);
    }




    @Override
    @Transactional
    public Project storeProjectProducts(Long projId, ProjectProductCollectionType collectionType, List<String> productsSkuCodes) {
        Project project = projRepo.findOne(projId);
        project.setProductsCollectionType(collectionType);

        List<ProjectProduct> productsNeedToBeCollected = Lists.newArrayList();
        for (String prodSkuCode : productsSkuCodes) {
            ProductLegacy prodLeg = productLegacyRepo.findOne(prodSkuCode);

            String brandCategoryCode = "";
            if (StringUtils.isBlank(brandCategoryCode = prodLeg.getBrandCategoryCode())) {//if null fetched then try another approach...
                if (StringUtils.isNotBlank(prodLeg.getBrandCode())) {
                    BrandLegacy brand = brandLegacyRepo.findOne(prodLeg.getBrandCode());
                    if (brand != null) brandCategoryCode = brand.getCategoryCode();
                }
            }

            productsNeedToBeCollected.add(new ProjectProduct(project, prodLeg.getCode(), prodLeg.getSkuName(), new Brand(prodLeg.getBrandCode(), brandCategoryCode)));
        }

        //clear first
        projProdRepo.deleteByProjectId(project.getId());

        project.setProductsNeedToBeCollected(productsNeedToBeCollected);//do batch refresh

        return projRepo.saveAndFlush(project);
    }

    private Project removeProjectEmployeesAndAllRelated(Project project) {

        List<ProjectEmployee> existEmps = project.getParticipatedEmployees();
        Map<Long, List<ProjectStore>> savedEmpsWithStoresAssigned = Maps.newHashMap();
        for (ProjectEmployee emp : existEmps) {
            if (emp.getEmpId() != null)
                savedEmpsWithStoresAssigned.put(emp.getEmpId(), emp.getAssignedStores());

            emp.unbindWithAllAssignedStores();//unbind with assigned stores
        }
        project.setParticipatedEmployees(existEmps);

        project = projRepo.saveAndFlush(project);

        projEmpRepo.deleteByProjectId(project.getId());

        projEmpRepo.flush();
        projRepo.flush();

        return project;
    }

    private Object[] removeProjectEmployeesAndAllRelatedWithSavedStoresAssignedReturn(Project project) {

        List<ProjectEmployee> existEmps = project.getParticipatedEmployees();
        Map<Long, List<ProjectStore>> savedEmpsWithStoresAssigned = Maps.newHashMap();
        for (ProjectEmployee emp : existEmps) {
            if (emp.getEmpId() != null && !CollectionUtils.isEmpty(emp.getAssignedStores()))
                savedEmpsWithStoresAssigned.put(emp.getEmpId(), emp.getAssignedStores());

            emp.unbindWithAllAssignedStores();//unbind with assigned stores
        }
        project.setParticipatedEmployees(existEmps);

        project = projRepo.saveAndFlush(project);

        projEmpRepo.deleteByProjectId(project.getId());

        projEmpRepo.flush();
        projRepo.flush();

        return new Object[]{project, savedEmpsWithStoresAssigned};
    }

    @Override
    public int saveProjectStore(List<ProjectStore> entities) {
        int n=0;
        for (ProjectStore projectStore : entities) {
            try {
                projStoreRepo.save(projectStore);
                n++;
            } catch (Exception e) {
                logger.warn("save projectStore "+projectStore.getChannelCode()+" duplicate");
            }
        }
        return n;
    }

    @Override
    public List<Long> findProjectIdListByOrganizationCodeList(List<String> organizationCodeList) {
        StringBuilder temp = new StringBuilder(200);
        Iterator<String> orgCodeIterator = organizationCodeList.iterator();
        if (orgCodeIterator.hasNext()) {
            temp.append("'").append(orgCodeIterator.next()).append("'");
        }
        while (orgCodeIterator.hasNext()) {
            temp.append(",'").append(orgCodeIterator.next()).append("'");
        }
        StringBuilder sql = new StringBuilder(256);
        sql.append("select p.id\n" +
                "from DM_PROJECT p\n" +
                "join DM_USER u on u.id=p.user_id\n" +
                "join MD_ORG_EMP org on org.emp_pk=u.emp_pk\n" +
                "where org.org_code in (").append(temp).append(")");
        sql.append("\nUNION\n" +
                "select p.id\n" +
                "from DM_PROJECT p\n" +
                "join DM_USER u on u.emp_pk=p.manager_emp_id\n" +
                "join MD_ORG_EMP org on org.emp_pk=u.emp_pk\n" +
                "where org.org_code in (").append(temp).append(")");
        List<Object> dataList = entityManager.createNativeQuery(sql.toString()).getResultList();
        List<Long> resultList = new ArrayList<Long>(dataList.size());
        for (Object data : dataList) {
            resultList.add(((Number) data).longValue());
        }
        return resultList;
    }

	@SuppressWarnings({ "rawtypes", "unchecked" })
	@Override
	public Page<SalesLegacy> findSalesPromoter(Map<String, String> parameterMap, Pageable pageable) {

		String storeCode=parameterMap.get("storeCode");
		String storeName=parameterMap.get("storeName");
		String catCode=parameterMap.get("category");
		String sysCatCode=parameterMap.get("sysCategory");

		Long projectId = Long.valueOf(parameterMap.get("projectId"));

		List<Map> allStores=new ArrayList<Map>(0);

		final String adId=parameterMap.get("adId");
		final String saleName=parameterMap.get("salesPromoterName");

		boolean hasSelectJoinStore=false;

		if(!StringUtils.isEmpty(storeCode) || !StringUtils.isEmpty(storeName) || !StringUtils.isEmpty(catCode) || !StringUtils.isEmpty(sysCatCode)){
			hasSelectJoinStore=true;
			Page<Map> allStoresPage = searchService.findProjectStore(parameterMap, new PageRequest(0, Integer.MAX_VALUE));
			allStores=allStoresPage.getContent();
		}

		//门店
		List saleIds=Lists.newArrayList();
		if(allStores.size()>0){
			StringBuffer idsByStore=new StringBuffer("select t.sales_promoter from DM_PROJECT_SALESPROMOTER t where t.id in (select t.projsales_id from DM_PROJECT_SALESPROMOTER_STORE t where t.projstore_id in (");
			for (int i = 0; i < allStores.size(); i++) {
				idsByStore.append(allStores.get(i).get("id"));
				if(i!=allStores.size()-1){
					idsByStore.append(",");
				}
			}
			idsByStore.append("))");
			saleIds = entityManager.createNativeQuery(idsByStore.toString()).getResultList();
		}

		if(saleIds.size()<1){
			if(hasSelectJoinStore && saleIds.size()<1){
				return new PageImpl<SalesLegacy>(new ArrayList(), pageable, 0);
			}else{
				saleIds = entityManager.createNativeQuery("select t.sales_promoter from DM_PROJECT_SALESPROMOTER t where t.project = "+projectId+" order by t.sales_promoter desc").getResultList();
				if(saleIds.size()<1){
					return new PageImpl<SalesLegacy>(new ArrayList(), pageable, 0);
				}
			}
		}

		final List salesList=saleIds;
		Specifications<SalesLegacy> spec=Specifications.where(new Specification<SalesLegacy>() {
			@Override
			public Predicate toPredicate(Root<SalesLegacy> root,CriteriaQuery<?> query, CriteriaBuilder cb) {
				Path path = root.get("code");
				In in = cb.in(path);
				for (int i = 0; i < salesList.size(); i++) {
					in.value(salesList.get(i));
				}
                return in;
			}
		});

		//行政区
		if(!StringUtils.isEmpty(adId)){

			AdminDivisionLegacy ad = adminDivLegacyRepo.findByCode(adId);
			if(ad!=null){

				int deep = ad.getDeep();
				List adIds=Lists.newArrayList();

				if(deep==1){
					adIds=adminDivLegacyRepo.findAdIdByParentProvinceAdName(ad.getName());
				}else if(deep==2){
					adIds=adminDivLegacyRepo.findAdIdByParentProvinceAdNameAndCityAdName(ad.getParentProvinceAdName(),
																					     ad.getName(),
																					     ad.getName()+"%");
				}else if(deep==3){
					adIds=adminDivLegacyRepo.findAdIdByParentProvinceAdNameAndCityAdNameAndAdName(ad.getParentProvinceAdName(),
						     																	  ad.getParentCityAdName(),
																								  ad.getName());
				}

				if(adIds.size()>0){
					final List adIdList = adIds;
					spec = spec.and(new Specification<SalesLegacy>() {
		                public Predicate toPredicate(Root<SalesLegacy> root,
		                                             CriteriaQuery<?> query,
		                                             CriteriaBuilder cb) {
		                    Path path = root.get("adId");
		    				In in = cb.in(path);
		    				for (int i = 0; i < adIdList.size(); i++) {
		    					in.value(adIdList.get(i));
		    				}
		                    return in;
		                }
		            });
				}else{
					return new PageImpl<SalesLegacy>(new ArrayList(), pageable, 0);
				}
			}


		}

		if(!StringUtils.isEmpty(saleName)){
			spec = spec.and(new Specification<SalesLegacy>() {
                public Predicate toPredicate(Root<SalesLegacy> root,
                                             CriteriaQuery<?> query,
                                             CriteriaBuilder cb) {
                    Path<String> path = root.get("name");
                    return cb.like(path, "%" + saleName + "%");
                }
            });
		}

		return salesLegacyRepository.findAll(spec, pageable);
	}

	@Override
	public List<String> filterStoreIdsBySaleAssigned(List<String> storeCodes,String saleId, long projectId) {
		if(storeCodes.size()<1){
			return storeCodes;
		}
		return salesLegacyRepository.filterStoreIdsBySaleAssigned(storeCodes,saleId,projectId);
	}

    @Transactional(propagation = Propagation.SUPPORTS)
    @Override
    public Page<AssignableProjectStoreDto> listAssignableProjectStores(String userLoginName, String projectCode, String storeName, Pageable pageable) {
        //yaba daba doo
        try {
            String sql = "select d.ad_code\n" +
                    "from dm_user u\n" +
                    "join md_emp e on e.emp_pk=u.emp_pk\n" +
                    "join md_admin_div d on d.ad_id=e.ad_id\n" +
                    "where u.login_name=:userLoginName";
            String adCode = null;
            try {
                Query query = entityManager.createNativeQuery(sql);
                query.setParameter("userLoginName", userLoginName);
                adCode = (String) query.getSingleResult();
            } catch (Exception e) {
                adCode = null;
            }
            StringBuilder mainSql = new StringBuilder();
            mainSql.append("select 0 sort_index, 0 mine, c.channel_code, c.channel_name, ps.id\n" +
                    "from dm_channel_sync c\n" +
                    "join dm_project_store ps on ps.channel=c.id\n" +
                    "join dm_project p on p.id=ps.project\n" +
                    "where p.code=:projectCode and c.channel_code='-1'\n");
            mainSql.append("union all\n");
            mainSql.append("select 1 sort_index, 1 mine, c.channel_code, c.channel_name, ps.id\n" +
                    "from dm_channel_sync c\n" +
                    "join dm_project_store ps on ps.channel=c.id\n" +
                    "join dm_project p on p.id=ps.project\n" +
                    "join dm_project_employee_store pes on pes.projstore_id=ps.id\n" +
                    "join dm_project_employee pe on pe.id=pes.projemp_id\n" +
                    "join dm_user u on u.emp_pk=pe.emp_id\n" +
                    "where p.code=:projectCode\n" +
                    "and u.login_name=:userLoginName\n");
            if (!storeName.isEmpty()) {
                mainSql.append("and c.channel_name like :storeName\n");
            }
            if (adCode != null) {
                mainSql.append("union all\n");
                mainSql.append("select 2 sort_index, 0 mine, c.channel_code, c.channel_name, ps.id\n" +
                        "from dm_channel_sync c\n" +
                        "join dm_project_store ps on ps.channel=c.id\n" +
                        "join dm_project p on p.id=ps.project\n" +
                        "left join dm_project_employee_store pes on pes.projstore_id=ps.id\n" +
                        "left join dm_project_employee pe on pe.id=pes.projemp_id\n" +
                        "left join dm_user u on u.emp_pk=pe.emp_id\n" +
                        "where p.code=:projectCode and (u.login_name is null or u.login_name<>:userLoginName)\n" +
                        "and (c.channel_ad_code=:adCode or c.channel_city_ad_code=:adCode or c.channel_province_ad_code=:adCode)\n");
                if (!storeName.isEmpty()) {
                    mainSql.append("and c.channel_name like :storeName\n");
                }
            }
            String countSql = "select count(*) from (" + mainSql + ")";
            Query query = entityManager.createNativeQuery(countSql);
            query.setParameter("userLoginName", userLoginName);
            query.setParameter("projectCode", projectCode);
            if (!storeName.isEmpty()) {
                query.setParameter("storeName", storeName);
            }
            if (!adCode.isEmpty()) {
                query.setParameter("adCode", adCode);
            }
            int total = ((Number) query.getSingleResult()).intValue();
            if (total == 0) {
                return new PageImpl<AssignableProjectStoreDto>(new ArrayList<AssignableProjectStoreDto>(0));
            } else {
                mainSql.append("order by sort_index, channel_name\n");
                String dataSql = "select * from ( select row_.*, rownum rownum_ from (" + mainSql +
                        ") row_ where rownum <= :to) where rownum_ > :from";
                query = entityManager.createNativeQuery(dataSql);
                query.setParameter("userLoginName", userLoginName);
                query.setParameter("projectCode", projectCode);
                if (!storeName.isEmpty()) {
                    query.setParameter("storeName", storeName);
                }
                if (!adCode.isEmpty()) {
                    query.setParameter("adCode", adCode);
                }
                query.setParameter("from", pageable.getOffset());
                query.setParameter("to", pageable.getOffset() + pageable.getPageSize());
                @SuppressWarnings("unchecked")
                List<Object[]> dataList = query.getResultList();
                List<AssignableProjectStoreDto> storeList = new LinkedList<AssignableProjectStoreDto>();
                for (Object[] data : dataList) {
                    AssignableProjectStoreDto store = new AssignableProjectStoreDto();
                    storeList.add(store);
                    store.setMine(((Number) data[1]).intValue());
                    store.setCode((String) data[2]);
                    store.setName((String) data[3]);
                    store.setId(((Number) data[4]).longValue());
                }
                return new PageImpl<AssignableProjectStoreDto>(storeList, pageable, total);
            }
        } catch (Exception e) {
            logger.error("get assignableStores", e);
            return new PageImpl<AssignableProjectStoreDto>(new ArrayList<AssignableProjectStoreDto>(0));
        }
    }

    
    
    
	@Override
	public List<Map<String,Object>> findProjectBasicInfoByUserPermission(User user) {
		if(!user.getPermissionRole().isAdvanced()){
			String sql="select t.id, t.code, t.name "+
					  " from DM_PROJECT t "+
					 " where t.id in (select t1.project "+
									"   from DM_PROJECT_EMPLOYEE t1 "+
									"  where t1.emp_id = ?) "+
									"  order by t.name ";
			return jdbcTemplate.queryForList(sql, new Object[]{user.getEmployee().getEmpId()});
		}else{
			String sql="select t.id, t.code, t.name from DM_PROJECT t order by t.name";
			return jdbcTemplate.queryForList(sql);
		}
	}
	
	
	
	
	
	
	
	
	
	
	
}
